<?php
include 'inc/conn.php';
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('PRC');

if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';

// 将富文本编辑器里面的内容过滤
function html_to_text($string){
    $string = trim($string);  
    $string = str_replace("<p>", "", $string);
    $string = str_replace("<br>", chr(10), $string);
    $string = str_replace("<br />", chr(10), $string);
    $string = str_replace("</p>", chr(10), $string);
	$string = str_replace("&amp;", "&", $string);
	$string = str_replace("&nbsp;", " ", $string);
	$string = str_replace("&gt;", ">", $string);
	$string = str_replace("&quot;", "\"", $string);
	if(!strstr($string, '<img')){
		$string = strip_tags($string);
	}
	return $string.PHP_EOL;
}

// 检查是否有图像的内容，有的话则攫取路径
function hasImg($string)
{
		if(strstr($string, '<img')){
			preg_match_all('/<img.*?src="(.*?)".*?>/is', $string, $result);
			if(strlen($result[1][0])<5){
				return "";
			}else{
				return str_replace("..", "", $result[1][0]);
			}
		}else{
			return "";
		}
}

// 获取当前的周别
function get_weeks_num(){
	$strTime = date("Y-m-d");
	$intWeek = ceil(((strtotime($strTime) - strtotime(date("Y")."-01-01 00:00:00")))/(7*86400));
	return $intWeek;
}

// 设置边框的数组
$styleThinBlackBorderOutline = array(
        'borders' => array(
            'allborders' => array( //设置全部边框
                'style' => \PHPExcel_Style_Border::BORDER_DASHDOTDOT //粗的是thick
				),
        ),
);

// 定义要创建的表格名称
$Dept = Array("EE", "TE", "PE", "RT");

// 定义要写入数据的开始行
$rEE=5;
$rTE=5;
$rPE=5;
$rRT=5;

// 创建PHPExcel对象
$objPHPExcel = new PHPExcel();

// 设置文档属性
$objPHPExcel->getProperties()->setCreator("TE")
							 ->setLastModifiedBy("TE")
							 ->setTitle("EE Weekly Report")
							 ->setSubject("EE Weekly Report")
							 ->setDescription("EE Weekly Report, generated using TE KS.")
							 ->setKeywords("TE, http://10.100.160.166")
							 ->setCategory("TE");

for($i=0;$i<4;$i++){
	// Add common Title
	$objPHPExcel->setActiveSheetIndex($i)
				->setCellValue('A5', 'Item') // 填写单元格内容
				->setCellValue('B5', 'Problem summary')
				->setCellValue('C5', 'What Risk will...?')
				->setCellValue('D5', 'What we did')
				->setCellValue('E5', 'What we can do?')
				->setCellValue('F5', 'Project')
				->setCellValue('G5', 'Trigger date')
				->setCellValue('H5', 'Issue date')
				->setCellValue('I5', 'Owner')
				->setCellValue('J5', 'Status')
				->setCellValue('K5', 'Remark')->getStyle("A5:K5")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  // 设置单元格对齐
				
	$objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(12);  // 设置第二行的行高
	
	$objPHPExcel->getActiveSheet()->getStyle("A5:K5")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);   // 设置字体颜色
	$objPHPExcel->getActiveSheet()->getStyle("A5:K5")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('538dd5');   // 设置单元格背景色
	$objPHPExcel->getActiveSheet()->getStyle("A1:K1")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ffffff');
	$objPHPExcel->getActiveSheet()->getStyle("A4:K4")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ffffff');
	$objPHPExcel->getActiveSheet()->getStyle("F2:F3")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ffffff');

	$objPHPExcel->getActiveSheet()->getStyle('A2:E3')->applyFromArray($styleThinBlackBorderOutline);  // 设置表格边框
	$objPHPExcel->getActiveSheet()->getStyle('G2:K3')->applyFromArray($styleThinBlackBorderOutline);	
	$objPHPExcel->getActiveSheet()->getStyle('A5:K5')->applyFromArray($styleThinBlackBorderOutline);

	$objPHPExcel->getActiveSheet()->mergeCells('A2:A3')->setCellValue('A2', "Problem".chr(10)."status".chr(10)."indicator")->getStyle('A2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('538dd5');
	$objPHPExcel->getActiveSheet()->getStyle('A2:A3')->getFont()->setName('Arial')->setSize(7);  // 设置字体及大小
	
	$objPHPExcel->getActiveSheet()->setCellValue('B2', '"C"')->getStyle('B2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('c00000');
	$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Crisis and high risk');
	$objPHPExcel->getActiveSheet()->setCellValue('C2', '"R"')->getStyle('C2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ff0000');
	$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Critical item and Risk');
	$objPHPExcel->getActiveSheet()->setCellValue('D2', '"Y"')->getStyle('D2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ffff00');
	$objPHPExcel->getActiveSheet()->setCellValue('D3', 'Minor item and Yellow');
	$objPHPExcel->getActiveSheet()->setCellValue('E2', '"T"')->getStyle('E2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('e6b8b7');
	$objPHPExcel->getActiveSheet()->setCellValue('E3', 'Tracking');

	$objPHPExcel->getActiveSheet()->mergeCells('G2:H2')->setCellValue('G2', "When?")->getStyle('G2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('538dd5');
	$objPHPExcel->getActiveSheet()->mergeCells('G3:H3')->setCellValue('G3', "WK".get_weeks_num());
	$objPHPExcel->getActiveSheet()->setCellValue('I2', 'Report by?')->getStyle('I2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('538dd5');
	$objPHPExcel->getActiveSheet()->setCellValue('I3', '=I6');
	$objPHPExcel->getActiveSheet()->mergeCells('j2:k2')->setCellValue('J2', 'Report by which team?')->getStyle('J2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('538dd5');
	$objPHPExcel->getActiveSheet()->mergeCells('j3:k3')->setCellValue('J3', $Dept[$i]);  // 设置单元格合并

	// 设置单元格自动换行
	$objPHPExcel->getActiveSheet()->getStyle("A2:K3")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

	$objPHPExcel->getActiveSheet()->getStyle("A")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("B")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("C")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("D")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
	$objPHPExcel->getActiveSheet()->getStyle("E")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
	$objPHPExcel->getActiveSheet()->getStyle("F")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("G")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("H")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("I")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("J")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("K")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

	$objPHPExcel->getActiveSheet()->getColumnDimension('A') -> setAutoSize(true);   // 设置列的宽度
	$objPHPExcel->getActiveSheet()->getColumnDimension('B') -> setWidth(17); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('C') -> setWidth(20); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('D') -> setWidth(56); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('E') -> setWidth(47); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('F') -> setAutoSize(true); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('G') -> setWidth(12); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('H') -> setWidth(12); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('I') -> setWidth(12); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('J') -> setAutoSize(true); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('K') -> setWidth(12); 

	$objPHPExcel->getActiveSheet()->freezePane('A6');	// 设置冻结行
	$objPHPExcel->getActiveSheet()->setAutoFilter('A5:K5');

/*	
	$autoFilter=$objPHPExcel->getActiveSheet()->getAutoFilter();  // 设置自动筛选固定调节？？不知道为何不执行
	$autoFilter->getColumn('J')
		->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER)
		->createRule()
		->setRule(
				PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
				'C')
		->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);
	$autoFilter->showHideRows();
*/
	$objPHPExcel->getActiveSheet()->setTitle($Dept[$i]);   // 设置表格名称
	if($i<3)	$objPHPExcel->createSheet();  // 创建新的表格
} 

	$sheet=0;
	$r=0;

	// 进入数据库查询周报的全部内容
	$db = new mysql();
	$sql = "select * from weekly_report where Status in ('Crisis','Critical','Minor','Tracking')";
	//$sql = "select * from weekly_report";
	$db->query($sql);
	$num = $db->db_num_rows();
	if ($num == 0)
		echo "No data was found!";
	for($i=0; $i<$num; $i++){
		$row = $db->fetch_assoc();
		
		if($row['DEPT']=="EE"){$sheet=0; $rEE++; $r=$rEE;};
		if($row['DEPT']=="TE"){$sheet=1; $rTE++; $r=$rTE;};
		if($row['DEPT']=="PE"){$sheet=2; $rPE++; $r=$rPE;};
		if($row['DEPT']=="RT"){$sheet=3; $rRT++; $r=$rRT;};
		
		$j=$r-5;

		$objPHPExcel->setActiveSheetIndex($sheet)
            ->setCellValue("A${r}", "${j}")
			->setCellValue("B${r}", html_to_text(htmlspecialchars_decode($row['Issues'])))
			->setCellValue("C${r}", html_to_text(htmlspecialchars_decode($row['Risks'])))
			->setCellValue("D${r}", html_to_text(htmlspecialchars_decode($row['WeDid'])))
			->setCellValue("E${r}", html_to_text(htmlspecialchars_decode($row['WillDo'])))
			->setCellValue("F${r}", html_to_text(htmlspecialchars_decode($row['Project'])))
			->setCellValue("G${r}", htmlspecialchars_decode($row['Trigger date']))
			->setCellValue("H${r}", htmlspecialchars_decode($row['Issue date']))
			->setCellValue("I${r}", htmlspecialchars_decode($row['Owner']))
			->setCellValue("K${r}", html_to_text(htmlspecialchars_decode($row['Remark'])));
			$objPHPExcel->getActiveSheet()->getStyle("D")->getAlignment()->setWrapText(true);
			$objPHPExcel->getActiveSheet()->getStyle("A${r}:K${r}")->applyFromArray($styleThinBlackBorderOutline);
			if($row['Status']=='Crisis')
				$objPHPExcel->getActiveSheet()->setCellValue("J${r}", "C")->getStyle("J${r}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('c00000');
			if($row['Status']=='Critical')
				$objPHPExcel->getActiveSheet()->setCellValue("J${r}", "R")->getStyle("J${r}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ff0000');
			if($row['Status']=='Minor')
				$objPHPExcel->getActiveSheet()->setCellValue("J${r}", "Y")->getStyle("J${r}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ffff00');
			if($row['Status']=='Tracking')
				$objPHPExcel->getActiveSheet()->setCellValue("J${r}", "T")->getStyle("J${r}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('e6b8b7');
			if($row['Status']=='Closed')
				$objPHPExcel->getActiveSheet()->setCellValue("J${r}", "Closed");
			
			$tmpStr=hasImg(htmlspecialchars_decode($row['WeDid']));
			if(strlen($tmpStr)>=5){
				$img=new PHPExcel_Worksheet_Drawing();
				$img->setPath('D:/WebSrv/htdocs'.$tmpStr);//写入图片路径
				$img->setHeight(200);//写入图片高度
				$img->setWidth(380);//写入图片宽度
				$img->setOffsetX(1);//写入图片在指定格中的X坐标值
				$img->setOffsetY(1);//写入图片在指定格中的Y坐标值
				$img->setRotation(0);//设置旋转角度
				$img->getShadow()->setVisible(false);
				$img->getShadow()->setDirection(0);
				$img->setCoordinates("D${r}");//设置图片所在表格位置
				$img->setWorksheet($objPHPExcel->getActiveSheet());//把图片写到当前的表格中
				$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight(200);
			}

			$tmpStr=hasImg(htmlspecialchars_decode($row['WillDo']));
			if(strlen($tmpStr)>=5){
				$img=new PHPExcel_Worksheet_Drawing();
				$img->setPath('D:/WebSrv/htdocs'.$tmpStr);//写入图片路径
				$img->setHeight(200);//写入图片高度
				$img->setWidth(380);//写入图片宽度
				$img->setOffsetX(1);//写入图片在指定格中的X坐标值
				$img->setOffsetY(1);//写入图片在指定格中的Y坐标值
				$img->setRotation(0);//设置旋转角度
				$img->getShadow()->setVisible(false);
				$img->getShadow()->setDirection(0);
				$img->setCoordinates("E${r}");//设置图片所在表格位置
				$img->setWorksheet($objPHPExcel->getActiveSheet());//把图片写到当前的表格中
				$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight(200);
			}

			$tmpStr=hasImg(htmlspecialchars_decode($row['Remark']));
			if(strlen($tmpStr)>=5){
				$img=new PHPExcel_Worksheet_Drawing();
				$img->setPath('D:/WebSrv/htdocs'.$tmpStr);//写入图片路径
				$img->setHeight(200);//写入图片高度
				$img->setWidth(380);//写入图片宽度
				$img->setOffsetX(1);//写入图片在指定格中的X坐标值
				$img->setOffsetY(1);//写入图片在指定格中的Y坐标值
				$img->setRotation(0);//设置旋转角度
				$img->getShadow()->setVisible(false);
				$img->getShadow()->setDirection(0);
				$img->setCoordinates("K${r}");//设置图片所在表格位置
				$img->setWorksheet($objPHPExcel->getActiveSheet());//把图片写到当前的表格中
				$objPHPExcel->setActiveSheetIndex($sheet)->setCellValue("L${r}", 'PIC');
				$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight(200);
			}
	}

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="EE_Weekly_Report-wk'.get_weeks_num().'.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_end_clean();
$objWriter->save('php://output');
exit;
?>